#!/usr/bin/env python3
"""
数据库连接调试脚本
详细检查数据库连接、配置和基本操作
"""
import os
import sys
import logging
import asyncio
from pathlib import Path
from datetime import datetime

# 添加项目根目录到路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

# 设置详细日志
logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler(f'logs/database_debug_{datetime.now().strftime("%Y%m%d_%H%M%S")}.log', encoding='utf-8')
    ]
)

logger = logging.getLogger(__name__)

def debug_environment_variables():
    """调试环境变量"""
    logger.info("=" * 60)
    logger.info("🔍 调试环境变量")
    logger.info("=" * 60)
    
    # 检查.env文件
    env_file = Path(__file__).parent / ".env"
    if env_file.exists():
        logger.info(f"✅ .env文件存在: {env_file}")
        with open(env_file, 'r', encoding='utf-8') as f:
            content = f.read()
            logger.info(f"📄 .env文件内容长度: {len(content)} 字符")
            # 不打印敏感信息，只显示键名
            lines = content.split('\n')
            for line in lines:
                if line.strip() and '=' in line:
                    key = line.split('=')[0]
                    logger.info(f"   {key}=***")
    else:
        logger.warning(f"⚠️ .env文件不存在: {env_file}")
    
    # 检查关键环境变量
    mysql_vars = [
        'MYSQL_HOST', 'MYSQL_PORT', 'MYSQL_USER', 
        'MYSQL_PASSWORD', 'MYSQL_DATABASE', 'MYSQL_CHARSET'
    ]
    
    logger.info("\n📋 检查MySQL环境变量:")
    for var in mysql_vars:
        value = os.getenv(var)
        if value:
            if 'PASSWORD' in var:
                logger.info(f"   {var}: ***")
            else:
                logger.info(f"   {var}: {value}")
        else:
            logger.warning(f"   {var}: 未设置")

def debug_database_config():
    """调试数据库配置"""
    logger.info("\n" + "=" * 60)
    logger.info("🔍 调试数据库配置")
    logger.info("=" * 60)
    
    try:
        from database.config import db_config, POOL_CONFIG
        
        logger.info("📋 数据库配置:")
        config_dict = db_config.to_dict()
        for key, value in config_dict.items():
            if 'password' in key.lower():
                logger.info(f"   {key}: ***")
            else:
                logger.info(f"   {key}: {value}")
        
        logger.info("\n📋 连接池配置:")
        for key, value in POOL_CONFIG.items():
            logger.info(f"   {key}: {value}")
            
    except Exception as e:
        logger.error(f"❌ 加载数据库配置失败: {e}")

def debug_database_connection():
    """调试数据库连接"""
    logger.info("\n" + "=" * 60)
    logger.info("🔍 调试数据库连接")
    logger.info("=" * 60)
    
    try:
        from database.connection import db_manager
        
        logger.info("🔄 初始化数据库连接池...")
        success = db_manager.initialize_pool()
        
        if success:
            logger.info("✅ 数据库连接池初始化成功")
            
            # 测试连接
            logger.info("🔄 测试数据库连接...")
            if db_manager.test_connection():
                logger.info("✅ 数据库连接测试成功")
                
                # 获取连接信息
                conn_info = db_manager.get_connection_info()
                logger.info("📋 连接信息:")
                for key, value in conn_info.items():
                    logger.info(f"   {key}: {value}")
                
                # 测试基本查询
                logger.info("🔄 测试基本查询...")
                try:
                    with db_manager.get_cursor() as cursor:
                        cursor.execute("SELECT VERSION()")
                        result = cursor.fetchone()
                        logger.info(f"✅ MySQL版本: {result[0]}")
                        
                        cursor.execute("SELECT DATABASE()")
                        result = cursor.fetchone()
                        logger.info(f"✅ 当前数据库: {result[0]}")
                        
                        cursor.execute("SHOW TABLES")
                        tables = cursor.fetchall()
                        logger.info(f"✅ 数据库表数量: {len(tables)}")
                        for table in tables:
                            logger.info(f"   - {table[0]}")
                            
                except Exception as e:
                    logger.error(f"❌ 基本查询测试失败: {e}")
                    
            else:
                logger.error("❌ 数据库连接测试失败")
        else:
            logger.error("❌ 数据库连接池初始化失败")
            
    except Exception as e:
        logger.error(f"❌ 数据库连接调试失败: {e}")
        import traceback
        logger.error(f"详细错误信息: {traceback.format_exc()}")

def debug_database_operations():
    """调试数据库基本操作"""
    logger.info("\n" + "=" * 60)
    logger.info("🔍 调试数据库基本操作")
    logger.info("=" * 60)
    
    try:
        from database.service import db_service
        
        logger.info("🔄 测试数据库服务...")
        
        # 测试获取股票数据
        logger.info("🔄 测试获取股票数据...")
        try:
            # 这里需要异步执行
            pass
        except Exception as e:
            logger.error(f"❌ 获取股票数据测试失败: {e}")
        
        # 测试数据状态检查
        logger.info("🔄 测试数据状态检查...")
        try:
            # 这里需要异步执行
            pass
        except Exception as e:
            logger.error(f"❌ 数据状态检查测试失败: {e}")
            
    except Exception as e:
        logger.error(f"❌ 数据库操作调试失败: {e}")

async def debug_async_operations():
    """调试异步数据库操作"""
    logger.info("\n" + "=" * 60)
    logger.info("🔍 调试异步数据库操作")
    logger.info("=" * 60)
    
    try:
        from database.service import db_service
        
        # 测试数据状态检查
        logger.info("🔄 测试数据状态检查 (异步)...")
        try:
            status = await db_service.check_data_freshness("AAPL.US")
            logger.info(f"✅ 数据状态检查成功: {status}")
        except Exception as e:
            logger.error(f"❌ 数据状态检查失败: {e}")
        
        # 测试获取股票数据
        logger.info("🔄 测试获取股票数据 (异步)...")
        try:
            df = await db_service.get_stock_data_as_dataframe("AAPL.US", days=5)
            logger.info(f"✅ 获取股票数据成功，记录数: {len(df)}")
        except Exception as e:
            logger.error(f"❌ 获取股票数据失败: {e}")
            
    except Exception as e:
        logger.error(f"❌ 异步数据库操作调试失败: {e}")

def debug_docker_mysql():
    """调试Docker MySQL容器"""
    logger.info("\n" + "=" * 60)
    logger.info("🔍 调试Docker MySQL容器")
    logger.info("=" * 60)
    
    try:
        import subprocess
        
        # 检查Docker容器状态
        logger.info("🔄 检查Docker容器状态...")
        result = subprocess.run(['docker', 'ps'], capture_output=True, text=True)
        
        if result.returncode == 0:
            logger.info("✅ Docker命令执行成功")
            if 'quantitative_mysql' in result.stdout:
                logger.info("✅ quantitative_mysql 容器正在运行")
                logger.info("📋 容器信息:")
                for line in result.stdout.split('\n'):
                    if 'quantitative_mysql' in line:
                        logger.info(f"   {line}")
            else:
                logger.warning("⚠️ quantitative_mysql 容器未运行")
        else:
            logger.error(f"❌ Docker命令执行失败: {result.stderr}")
            
        # 检查MySQL端口
        logger.info("🔄 检查MySQL端口...")
        result = subprocess.run(['netstat', '-an'], capture_output=True, text=True)
        if result.returncode == 0:
            if ':3306' in result.stdout:
                logger.info("✅ MySQL端口 3306 正在监听")
            else:
                logger.warning("⚠️ MySQL端口 3306 未监听")
                
    except Exception as e:
        logger.error(f"❌ Docker MySQL调试失败: {e}")

def main():
    """主函数"""
    logger.info("🚀 开始数据库调试...")
    logger.info(f"📅 调试时间: {datetime.now()}")
    
    # 1. 调试环境变量
    debug_environment_variables()
    
    # 2. 调试数据库配置
    debug_database_config()
    
    # 3. 调试Docker MySQL
    debug_docker_mysql()
    
    # 4. 调试数据库连接
    debug_database_connection()
    
    # 5. 调试数据库基本操作
    debug_database_operations()
    
    # 6. 调试异步操作
    asyncio.run(debug_async_operations())
    
    logger.info("\n" + "=" * 60)
    logger.info("🏁 数据库调试完成")
    logger.info("=" * 60)

if __name__ == "__main__":
    main()
